#!/usr/bin/env python3
import sqlite3
import os
from datetime import datetime

def fix_duplicate_apps():
    """修复容器内的重复应用问题"""
    db_path = './openmemory.db'
    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()
    
    # 查看所有重复名称的应用
    cursor.execute('''
        SELECT name, COUNT(*) as count, GROUP_CONCAT(id) as ids
        FROM apps
        GROUP BY name
        HAVING count > 1
    ''')
    duplicates = cursor.fetchall()
    
    if not duplicates:
        print("没有发现重复应用")
        return
    
    print("发现重复应用:", duplicates)
    
    # 对于每个重复名称，保留最新的一个，删除其他的
    for name, count, ids_str in duplicates:
        ids = ids_str.split(',')
        print(f"处理重复应用 '{name}': {ids}")
        
        # 获取每个应用的创建时间
        cursor.execute('''
            SELECT id, created_at FROM apps WHERE id IN (%s) ORDER BY created_at DESC
        ''' % ','.join(['?' for _ in ids])), ids)
        apps_info = cursor.fetchall()
        
        # 保留最新的应用
        keep_app_id = apps_info[0][0]
        delete_app_ids = [app[0] for app in apps_info[1:]]
        
        print(f"保留应用: {keep_app_id}")
        print(f"删除应用: {delete_app_ids}")
        
        # 将被删除应用的记忆转移到保留的应用
        for delete_app_id in delete_app_ids:
            cursor.execute('''
                UPDATE memories 
                SET app_id = ? 
                WHERE app_id = ?
            ''', (keep_app_id, delete_app_id))
            
            print(f"转移了 {cursor.rowcount} 条记忆")
            
            # 删除重复应用
            cursor.execute('DELETE FROM apps WHERE id = ?', (delete_app_id,))
            print(f"删除了应用 {delete_app_id}")
    
    conn.commit()
    conn.close()
    print("重复应用修复完成")

if __name__ == "__main__":
    fix_duplicate_apps()